﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALOnSiteSign : Base.BaseService
    {
        private DataTable BuildTable()
        {
            DataTable dt = new DataTable();
            DataColumn dc;

            //考试人员
            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");     //该列的数据类型
            dc.ColumnName = "StudentName_KS";                       //该列的名称
            dc.AllowDBNull = false;                                 //该列是否可空
            dc.Caption = "StudentName_KS";                          //该列的标题   
            dc.Unique = false;                                      //该列是否具有唯一性
            dt.Columns.Add(dc);
            //考试人员
            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");     //该列的数据类型
            dc.ColumnName = "AllowExamID_KS";                       //该列的名称
            dc.AllowDBNull = false;                                 //该列是否可空
            dc.Caption = "AllowExamID_KS";                          //该列的标题   
            dc.Unique = false;                                      //该列是否具有唯一性
            dt.Columns.Add(dc);
            //候考人员
            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");     //该列的数据类型
            dc.ColumnName = "StudentName_ZB";                       //该列的名称
            dc.AllowDBNull = false;                                 //该列是否可空
            dc.Caption = "StudentName_ZB";                          //该列的标题   
            dc.Unique = false;                                      //该列是否具有唯一性
            dt.Columns.Add(dc);
            //候考人员
            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");     //该列的数据类型
            dc.ColumnName = "AllowExamID_ZB";                       //该列的名称
            dc.AllowDBNull = false;                                 //该列是否可空
            dc.Caption = "AllowExamID_ZB";                          //该列的标题   
            dc.Unique = false;                                      //该列是否具有唯一性
            dt.Columns.Add(dc);
            //候考人员
            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.String");     //该列的数据类型
            dc.ColumnName = "SignedID_ZB";                          //该列的名称
            dc.AllowDBNull = false;                                 //该列是否可空
            dc.Caption = "SignedID_ZB";                             //该列的标题   
            dc.Unique = false;                                      //该列是否具有唯一性
            dt.Columns.Add(dc);

            return dt;
        }
        private static DataTable CombinationDataTable = null;
        public DALOnSiteSign()
        {
            CombinationDataTable = BuildTable();
        }

        public DataTable GetTB_Pad_SignedTableTopOne_New(string strEntretenimientohID, string strExamYear)
        {
            string strSql = @" select  top 1
                            A.SignedID,
                            A.CreateTiime,
                            A.SignedTime,
                            C.Name,
                            B.AllowExamID,
                            C.NMT_SignUpID,
                            C.IdentityCard,
                            C.LinkMobile,
                            D.DicName as 'ExamProvinceName',
                            A.LotNO,
                            A.GroupNO,
                            B.ExamPlaceNO,
                            C.ExamProvince
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
		                    left join TB_Dic_ExamProvince              as D on C.ExamProvince=D.DicNO 
                        WHERE A.State =1 and A.EntretenimientohID = '{0}' and A.ExamYear='{1}' and ISNULL(AllowExamID,'')!='' and ISNULL(A.SignedTime,'')!=''
                        order by A.SignedTime asc";
            strSql = string.Format(strSql, strEntretenimientohID, strExamYear);
            return base.GetTable(strSql);
        }

        public DataTable CheckTB_Pad_Signed_New(string SpecialityKindsNO, string ExamLevel, string ExamPlaceNO, string idCard, string strExamYear)
        {
            string strSql = @" select a.SignedID,a.LotNO,a.State,a.SignedTime,a.GroupNO,b.*,c.NMT_SignUpID, c.IdentityCard,c.ExamProvince,c.LinkMobile, c.Name, d.DicName as 'ExamProvinceName' 
                 from TB_Exam_Signed as a
                left join TB_Exam_SignUpStudentsSpeciality as b on a.SignUpStudentsSpecialityID = b.SignUpStudentsSpecialityID
                left join TB_SignUpStudents as c on c.SignUpID = b.SignUpID
                LEFT JOIN TB_Dic_ExamProvince AS D ON C.ExamProvince = D.DicNO
                where b.ExamPlaceNO = '{0}' and b.ExamLevel = '{1}' and b.SpecialityKindsNO = '{2}' and c.IdentityCard='{3}' and a.ExamYear='{4}'  
            ";
            strSql = string.Format(strSql, ExamPlaceNO, ExamLevel, SpecialityKindsNO, idCard, strExamYear);
            return GetTable(strSql);
        }

        public DataTable GetTB_Pad_SignedTable_New(string strEntretenimientohID, string strExamYear)
        {
            string strSql = @" select 
                            A.SignedID,
                            A.CreateTiime,
                            A.SignedTime,
                            C.Name,
                            B.AllowExamID,
                            C.NMT_SignUpID,
                            C.IdentityCard,
                            C.LinkMobile,
                            D.DicName as 'ExamProvinceName',
                            A.LotNO,
                            A.GroupNO,
                            B.ExamPlaceNO,
                            C.ExamProvince
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
		                    left join TB_Dic_ExamProvince              as D on C.ExamProvince=D.DicNO 
                        WHERE A.State =1 and A.EntretenimientohID = '{0}' and A.ExamYear='{1}' and ISNULL(AllowExamID,'')!='' and ISNULL(A.SignedTime,'')!=''
                        order by A.SignedTime asc";
            strSql = string.Format(strSql, strEntretenimientohID, strExamYear);
            return base.GetTable(strSql);
        }

        public DataTable GetCombinationData(string strExamRoomNo, string strExamYear)
        {
            string strStudentName_KS;
            string strAllowExamID_KS;
            string strStudentName_ZB;
            string strAllowExamID_ZB;
            string strSignedID_ZB;
            #region sql
            string sqlKS = @"
                            select SignedID,Name AS StudentName,AllowExamID,B.ExamRoomName 
                                from TB_Pad_Examing		A 
                                left join TB_Exam_ExamRoom	B ON A.ExamRoomNO = B.ExamRoomNO 
                                where A.ExamRoomNO = '{0}' and A.ExamYear='{1}'
                                order by LotNO; 
                            ";
            string sqlHK = @"
                            select SignedID,Name AS StudentName,AllowExamID,B.ExamRoomName 
                                from TB_Pad_Queue		A
                                left join TB_Exam_ExamRoom	B ON A.ExamRoomNO = B.ExamRoomNO
                                where A.ExamRoomNO = '{0}' and A.ExamYear='{1}'
                                order by LotNO;
                            ";
            #endregion
            sqlKS = string.Format(sqlKS, strExamRoomNo, strExamYear);
            sqlHK = string.Format(sqlHK, strExamRoomNo, strExamYear);

            using (DataSet ds = GetDataSet(sqlKS + " " + sqlHK))
            {
                DataTable dtKS = ds.Tables[0];
                DataTable dtHK = ds.Tables[1];
                if (CombinationDataTable == null)
                {
                    CombinationDataTable = BuildTable();
                }
                DataTable dt = CombinationDataTable.Clone();
                DataRow dr;
                int iCount = dtKS.Rows.Count > dtHK.Rows.Count ? dtKS.Rows.Count : dtHK.Rows.Count;
                for (int i = 0; i < iCount; i++)
                {
                    strStudentName_KS = "";
                    strAllowExamID_KS = "";
                    strStudentName_ZB = "";
                    strAllowExamID_ZB = "";
                    strSignedID_ZB = "";
                    try
                    {
                        if (i < dtKS.Rows.Count)
                        {
                            strStudentName_KS = dtKS.Rows[i]["StudentName"].ToString();
                        }
                    }
                    catch
                    { }
                    try
                    {
                        if (i < dtKS.Rows.Count)
                        {
                            strAllowExamID_KS = dtKS.Rows[i]["AllowExamID"].ToString();
                        }
                    }
                    catch
                    { }
                    try
                    {
                        if (i < dtHK.Rows.Count)
                        {
                            strStudentName_ZB = dtHK.Rows[i]["StudentName"].ToString();
                        }
                    }
                    catch
                    { }
                    try
                    {
                        if (i < dtHK.Rows.Count)
                        {
                            strAllowExamID_ZB = dtHK.Rows[i]["AllowExamID"].ToString();
                        }
                    }
                    catch
                    { }
                    try
                    {
                        if (i < dtHK.Rows.Count)
                        {
                            strSignedID_ZB = dtHK.Rows[i]["SignedID"].ToString();
                        }
                    }
                    catch
                    { }
                    dr = dt.NewRow();
                    dr["StudentName_KS"] = strStudentName_KS;
                    dr["AllowExamID_KS"] = strAllowExamID_KS;
                    dr["StudentName_ZB"] = strStudentName_ZB;
                    dr["AllowExamID_ZB"] = strAllowExamID_ZB;
                    dr["SignedID_ZB"] = strSignedID_ZB;
                    dt.Rows.Add(dr);
                }
                return dt;
            }
        }

        public DataTable CheckTB_Pad_Signed(string SpecialityKindsNO, string ExamLevel, string ExamPlaceNO, string idCard)
        {
            string strSql = @"select a.*,B.NMT_SignUpID, B.IdentityCard,B.ExamProvince,B.LinkMobile, B.Name, C.DicName as 'ExamProvinceName' 
                             from TB_Exam_SignUpStudentsSpeciality AS A  
                            LEFT JOIN TB_SignUpStudents  AS B ON A.SignUpID=B.SignUpID	
                            LEFT JOIN TB_Dic_ExamProvince AS C ON B.ExamProvince=C.DicNO
                            where A.IsActive='1' and A.ExamPlaceNO='{0}' and A.SpecialityKindsNO='{1}' and (A.ExamLevel='{2}') and B.IdentityCard='{3}'
        ";// or A.ExamLevel='{4}'
            strSql = string.Format(strSql, ExamPlaceNO, SpecialityKindsNO, ExamLevel, idCard);

            return base.GetTable(strSql);
        }

        public DataTable GetTB_Pad_SignedTableByIdCard(string strEntretenimientohID, string strIdentityCard, string strExamYear)
        {
            string strSql = @" select 
                            A.*
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
                        WHERE A.EntretenimientohID = '{0}' and C.IdentityCard='{1}' and A.ExamYear='{2}'";
            strSql = string.Format(strSql, strEntretenimientohID, strIdentityCard, strExamYear);
            return base.GetTable(strSql);
        }

        public DataTable GetTB_Pad_SignedTable(string strSignedID)
        {
            string strSql = @" select 
                            A.SignedID,
                            A.CreateTiime,
                            A.ExamYear,
                            C.Name,
                            B.AllowExamID,
                            C.NMT_SignUpID,
                            C.IdentityCard,
                            C.LinkMobile,
                            D.DicName as 'ExamProvinceName',
                            A.LotNO,
                            A.GroupNO,
                            B.ExamPlaceNO,
                            C.ExamProvince
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
		                    left join TB_Dic_ExamProvince              as D on C.ExamProvince=D.DicNO 
                        WHERE A.SignedID = '{0}'";
            strSql = string.Format(strSql, strSignedID);
            return base.GetTable(strSql);
        }

        public DataTable GetTB_Pad_SignedTableTopOne(string strEntretenimientohID, string isOrder)
        {
            string strSql = @" select  top 1
                            A.SignedID,
                            A.CreateTiime,
                            C.Name,
                            B.AllowExamID,
                            C.NMT_SignUpID,
                            C.IdentityCard,
                            C.LinkMobile,
                            D.DicName as 'ExamProvinceName',
                            A.LotNO,
                            A.GroupNO,
                            B.ExamPlaceNO,
                            C.ExamProvince
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
		                    left join TB_Dic_ExamProvince              as D on C.ExamProvince=D.DicNO 
                        WHERE A.State =1 and A.EntretenimientohID = '{0}' and ISNULL(AllowExamID,'')!=''
                        order by A.LotNO {1}";
            if (Convert.ToBoolean(isOrder))
            {
                strSql = string.Format(strSql, strEntretenimientohID, "asc");
            }
            else
            {
                strSql = string.Format(strSql, strEntretenimientohID, "desc");
            }
            return base.GetTable(strSql);
        }

        public DataTable GetTB_Pad_SignedTable(string strEntretenimientohID, string isOrder)
        {
            string strSql = @" select 
                            A.SignedID,
                            A.CreateTiime,
                            C.Name,
                            B.AllowExamID,
                            C.NMT_SignUpID,
                            C.IdentityCard,
                            C.LinkMobile,
                            D.DicName as 'ExamProvinceName',
                            A.LotNO,
                            A.GroupNO,
                            B.ExamPlaceNO,
                            C.ExamProvince
                            from TB_Exam_Signed A 
                            left join TB_Exam_SignUpStudentsSpeciality as B on A.SignUpStudentsSpecialityID=b.SignUpStudentsSpecialityID 
		                    left join TB_SignUpStudents                as C on B.SignUpID=C.SignUpID 
		                    left join TB_Dic_ExamProvince              as D on C.ExamProvince=D.DicNO 
                        WHERE A.State =1 and A.EntretenimientohID = '{0}' and ISNULL(AllowExamID,'')!=''
                        order by A.LotNO {1}";
            if (Convert.ToBoolean(isOrder))
            {
                strSql = string.Format(strSql, strEntretenimientohID, "asc");
            }
            else
            {
                strSql = string.Format(strSql, strEntretenimientohID, "desc");
            }
            return base.GetTable(strSql);
        }
        public int GetQueueCount(string ExamRoomID)
        {
            string sqlQueue = @"select COUNT(1) from TB_Pad_Queue where ExamRoomNO = '{0}'";
            sqlQueue = string.Format(sqlQueue, ExamRoomID);
            return base.ToScalar<int>(sqlQueue);
        }
    }
}
